#    Copyright 2012 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.

#************************BEGIN Add Include Files to Path************************
import sys
NeededPaths=['/opt/coopip/djangoproject/include', '/opt/coopip/djangoproject']
for path in NeededPaths:
    if(not path in sys.path):
        sys.path.append(path)
#************************END   Add Include Files to Path************************
import os, csv, shutil, datetime, time, reportlab, djangoproject.settings, random
from django.contrib.gis.db import models
from django.contrib.gis.geos import *
from django.http import HttpResponse
from django.contrib import admin
from django.template import Context, loader
import djangoproject.settings
import _mysql as mysql
from models import *
from django import forms
import django
import djangoproject
import COOPIP_Ajax
import COOPIP_Misc
import COOPIP_Form, COOPIP_DynamicTemplate, COOPIP_Authentication, COOPIP_FormIntranet, COOPIP_FormGeneral
import GIS, CIS

def ReportMainMenu(request):
    t = loader.get_template('/opt/coopip/djangoproject/templates/reportmainmenu.html')
    c = Context({})
    return HttpResponse(t.render(c))

def MeterRateClass03DemandReport(request):
    """This report shows the demand data for meters in Cycle 03 for the past 30 days, with totals for each day."""
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_RevenueProtectionUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/revenueprotection.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
    GenericReportTitle= 'Demand Report For Cycle 03 Meters'
    Temp=[]
    import datetime
    StartDate=datetime.datetime.today() - datetime.timedelta(days=31)
    EndDate=datetime.datetime.today()
    AMRMeters=AMRSerialNumber.objects.filter(meterrateclass='03')
    CurrentDate = StartDate
    while(CurrentDate < EndDate):
        CurrentDateKey=MeterRead().DateTimeToKey(CurrentDate)
        Total = 0.0
        for amrmeter in AMRMeters:
            try:
                meter = Meter.objects.filter(SerialNumber=amrmeter.serialnumber)[0]
                meterRead = MeterRead.objects.filter(Meter=meter)[0]
            except IndexError:
                continue
            KWDemandDict=meterRead.GetKWDemandDict()
            if(KWDemandDict.has_key(CurrentDateKey)):
                Temp.append(str(CurrentDateKey).ljust(15) + str(meterRead.Meter.SerialNumber).ljust(20) + str(KWDemandDict[CurrentDateKey]))
                Total = Total + KWDemandDict[CurrentDateKey]
        Temp.append(str(CurrentDateKey).ljust(15) + 'TOTAL:'.ljust(20) + str(Total))
        CurrentDate = CurrentDate + datetime.timedelta(days=1)
    Preformatted='\n'.join(Temp)
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))

def GetARPCacheList(Destination, UserName, Password):
    import telnetlib, time, re
    tn=telnetlib.Telnet(Destination)
    tn.read_very_eager()
    time.sleep(2)
    tn.write(UserName + '\r')
    tn.read_until('word:')
    tn.write(Password + '\r')
    tn.read_very_eager()
    tn.write('terminal length 0\r')
    tn.write('clear arp-cache\r')
    tn.write('show arp\r')
    Output=''
    Tries=0
    while len(Output) < 1000 and Tries < 10:
        Output=tn.read_very_eager()
        time.sleep(2)
        Tries+=1
    Output=Output.split('\r\n')
    ReturnList=[]
    for i in Output[6:]:
        try:
            clearentry=re.sub(r'\s+', ' ', i)
            clearentry=clearentry.split(' ')
            ReturnList.append((clearentry[1], clearentry[3]))
        except:
            continue
    return ReturnList


def NetworkDeviceInventoryCheck(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_RevenueProtectionUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/revenueprotection.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
    GenericReportTitle= 'Temporary Report, will integrate into device inventory page when feasible'
    Report=[]
    ARPList=[]
    ARPList=GetARPCacheList(Destination=djangoproject.settings.SMORouterIPAddress,
                            UserName=djangoproject.settings.SMORouterUserName,
                            Password=djangoproject.settings.SMORouterPassword)
    ARPList+=GetARPCacheList(Destination=djangoproject.settings.RDORouterIPAddress,
                             UserName=djangoproject.settings.SMORouterUserName,
                             Password=djangoproject.settings.SMORouterPassword)
    ARPList+=GetARPCacheList(Destination=djangoproject.settings.SDORouterIPAddress,
                             UserName=djangoproject.settings.SMORouterUserName,
                             Password=djangoproject.settings.SMORouterPassword)
    ARPDict={}
    for i in ARPList:
        IPAddress, MACAddress=i
        ARPDict[MACAddress]=IPAddress
    NoIPList=[]
    NoMACList=[]
    for i in NetworkDevice.objects.all().order_by('IPAddress'):
        if not i.MACAddress:
            NoMACList.append(i)
        if not i.IPAddress:
            NoIPList.append(i)
    Report.append('\nDevices Missing IP Addresses')
    for i in NoIPList:
        Report.append(str(i.id).ljust(10) + i.Name)
    Report.append('\nDevices Missing MAC Addresses')
    for i in NoMACList:
            Report.append(str(i.id).ljust(10) + i.Name.ljust(50))
    Report.append('\nNetworkDevices Not in ARP (Not Recently Pinged or Offline)')
    for i in NetworkDevice.objects.all():
        if not i.MACAddress:
            continue
        if i.MACAddress in ARPDict:
            continue
        Report.append(i.Name.ljust(50) + i.MACAddress)
    Report.append('\nARP Not in NetworkDevices')
    for i in ARPList:
        IPAddress, MACAddress=i
        Devices=NetworkDevice.objects.filter(MACAddress=MACAddress)
        if Devices.count():
            device=Devices[0]
            if not device.IPAddress==IPAddress:
                Report.append('IPAddress Mismatch: ' + device.Name + ' NetworkDevice: ' + device.IPAddress + '  ARP: ' + IPAddress)
            continue
        Report.append(str(i))
    Preformatted='\n'.join(Report)
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))






def MeterBlinkRecent(request):
    """This report shows how many meter blinks there were recently for all meters in a table."""
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_RevenueProtectionUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/revenueprotection.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
    GenericReportTitle= 'Meter Blink Recent'
    Temp=[]
    import datetime
    StartDate=datetime.datetime.today() - datetime.timedelta(days=90)
    EndDate=datetime.datetime.today() - datetime.timedelta(days=0)
    MeterBlinks=MeterBlink.objects.all().order_by('-Meter')
    CurrentDate = StartDate
    MeterResultsDict={}  #Dict where meter results are stored  key = Meter Number, Value = Dict with key of date, value of number of blinks since previous day
    PotentialDates=[]
    while(CurrentDate < EndDate):
        PotentialDates.append(str(CurrentDate.year) + '-' +  str(CurrentDate.month).rjust(2, '0') + '-' + str(CurrentDate.day).rjust(2, '0') )
        CurrentDate = CurrentDate + datetime.timedelta(days=1)

    RawBlinkRows=[]
    Meter.objects.all().update(RecentMeterBlink=0)
    for meterBlink in MeterBlinks:
        RawBlinks=meterBlink.GetSpecifiedRawCounts(PotentialDates, BlankDatesWithPrevious=True)
        if('' in RawBlinks):
            continue
        BlinksFound=0
        CalculatedBlinks=[]

        Previous=RawBlinks[0]
        for i in RawBlinks[1:]:
            CalculatedBlinks.append(str(int(i) - int(Previous)))
            BlinksFound=BlinksFound + int(i) - int(Previous)
            Previous=i
        if(BlinksFound):
            try:
                CircuitNumber=int(meterBlink.Meter.CircuitNumber)
            except:
                CircuitNumber='UNKNOWN'
            RawBlinkRows.append([meterBlink.Meter.SerialNumber, str(meterBlink.Meter.GISCircuitNumber), str(BlinksFound)] + CalculatedBlinks)
            meterBlink.Meter.RecentMeterBlink=int(BlinksFound)
            meterBlink.Meter.save()
    import operator
    RawBlinkRows.sort(key=operator.itemgetter(2,1))

    DatesHeadingString=''
    for date in PotentialDates[:-1]:
        DatesHeadingString = DatesHeadingString + str(date).ljust(15)
    Temp.append('SerialNumber'.ljust(25) + 'CircuitNumber'.ljust(15) + 'Blinks'.ljust(10) + DatesHeadingString)
    for row in RawBlinkRows:
        DatesRow=[]
        for i in range(0, len(PotentialDates) - 1):
            DatesRow.append(row[3 + i].ljust(15))
        Temp.append(str(row[0]).ljust(25) + str(row[1]).ljust(15) + str(row[2]).ljust(10) + ''.join(DatesRow))
    Preformatted='\n'.join(Temp)
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))


def NormalizeCheckInDCSI(Value):
    import re
    Input=re.sub('\D', '', Value).lstrip('0')
    if Input.isdigit():
        return Input
    return False


def WarehouseJunkedMeters(request):
    """This report checks our known-junked meters from warehouse inventory against CIS meter records."""
    import django.db

    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_RevenueProtectionUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/revenueprotection.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
    GenericReportTitle= 'Warehouse Junked Meters'
    Temp=[]
    Meters=GIS.Warehousemeterinventorycheckin.objects.filter(junkindicator=True).order_by('id')
    InvalidDCSI=[]
    NoMatch=[]
    MatchedNotMarked=[]
    MatchedAndMarked=[]
    for i in Meters:
        DCSI=NormalizeCheckInDCSI(i.meterdcsinumber)
        if not DCSI:
            InvalidDCSI.append(i)
            continue
        DCSI=DCSI.rjust(12,'0')
        CISMeters=CIS.Meterdetl.objects.filter(meter=DCSI)
        if not CISMeters.count():
            NoMatch.append((i, DCSI))
            continue
        meter=CISMeters[0]
        if meter.junkedcode=='01':
            MatchedAndMarked.append(DCSI)
        else:
            MatchedNotMarked.append(DCSI)
    Temp.append('Found and Marked in CIS: ' + str(len(MatchedAndMarked)))
    Temp.append('\n\n')
    Temp.append('Found In Warehouse Inventory But Not Marked in CIS: ' + str(len(MatchedNotMarked)))
    for i in MatchedNotMarked:
        Temp.append(i)
    Temp.append('\n\n')
    Temp.append('Could Not Find Matching DCSI in CIS: ' + str(len(NoMatch)))
    for i in NoMatch:
        meter, DCSI=i
        Temp.append(DCSI)
    Preformatted='\n'.join(Temp)
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))


def TruckHistory(request, TruckNumber, Days):
    """This report History of Truck Locations and allows clicking on the Truck Location on Google Maps
       Replace the URL with a form at a later date"""
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_RevenueProtectionUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/revenueprotection.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
    Temp=[]
    import datetime
    StartDate=datetime.datetime.today() - datetime.timedelta(days=int(Days))
    EndDate=datetime.datetime.today()
    TruckLogs=TruckLog.objects.filter(Number=int(TruckNumber)).filter(CreateDateTime__gte=StartDate).order_by('DateTime').order_by('CreateDateTime')
    ResultLogs=[]
    FirstLoop=True
    for i in TruckLogs:
        if(FirstLoop):
            CurrentLog = i
        if(i.CreateDateTime - CurrentLog.CreateDateTime).seconds > (60 * 5) or FirstLoop:
            p = i.Point.transform(4326, clone=True)
            x = str(abs(p.x))
            y = str(p.y)
            ResultLogs.append(str(TruckNumber) + '  ' + str(i.CreateDateTime)[:19].ljust(21) + str(i.Point.distance(CurrentLog.Point))[:6].ljust(10) +' ft  ' + \
            '<a href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=' + x + 'W+' + y + 'N&sll=' + x + ',' + y + '">Google Maps</a>')
            CurrentLog = i
            FirstLoop = False
    Logs = '<br>'.join(ResultLogs)
    t = loader.get_template('/opt/coopip/djangoproject/templates/reporttruckhistory.html')
    c = Context({'LoginStatus' : LoginStatus, 'Logs': Logs, 'TruckNumber': TruckNumber, 'Days': Days })
    return HttpResponse(t.render(c))





